﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;

namespace HREDU.Hoang_Tuan.Classes
{
    class CCTQTDT
    {
        private int _id_ctqtdt;
        private int _id_nhanvien;
        private string _truong;
        private string _chuyennganh;
        private string _tunam;
        private string _dennam;
        private int _id_hdt;
        private int _id_vb;

         public CCTQTDT()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        #region "Properties"

        public int ID_CTQTDT
        {
            get { return _id_ctqtdt; }
            set { _id_ctqtdt = value; }
        }

        public int ID_NHANVIEN
        {
            get { return _id_nhanvien; }
            set { _id_nhanvien = value; }
        }

        public string TRUONG
        {
            get { return _truong; }
            set { _truong = value; }
        }

        public string CHUYENNGANH
        {
            get { return _chuyennganh; }
            set { _chuyennganh = value; }
        }

        public string TUNAM
        {
            get { return _tunam; }
            set { _tunam = value; }
        }

        public string DENNAM
        {
            get { return _dennam; }
            set { _dennam = value; }
        }

        public int ID_HDT
        {
            get { return _id_hdt; }
            set { _id_hdt = value; }
        }

        public int ID_VB
        {
            get { return _id_vb; }
            set { _id_vb = value; }
        }


        #endregion

        public int ColumnLength(string column)
        {
            return (int)MITI.BaseDB.Instance.ExecSql_DataValue("select COL_LENGTH('CTQTDT','" + column + "')") / 2;
        }

        ///// <summary>
        ///// Kiem tra ton theo id_nbl va id_nhanvien
        ///// </summary>
        ///// <returns></returns>
        //public bool isExists()
        //{
        //    string strSQL = "select count(*) from view_QTKhenThuong where id_nbl = " + _id_nbl + " and id_nhanvien = " + _id_nhanvien;
        //    int value = (int)MITI.BaseDB.Instance.ExecSql_DataValue(strSQL);
        //    if (value > 0)
        //    {
        //        return true;
        //    }
        //    return false;
        //}

        /// <summary>
        /// Lấy thuộc tính của lớp đối tượng theo string name.
        /// Dùng hàm này để các hàm insert, update có thể dùng câu lệnh for cho các thuộc tính
        /// </summary>
        /// <param name="paramName">The name of the parameter.</param>
        /// <param name="value">The value of the parameter.</param>
        /// <returns>Giá trị của thuộc tính đó.</returns>
        /// 
        public object GetValueByName(string paramName)
        {
            object obj = null;
            switch (paramName)
            {
                case "ID_CTQTDT":
                    obj = _id_ctqtdt;
                    break;
                case "ID_NHANVIEN":
                    obj = _id_nhanvien;
                    break;
                case "TRUONG":
                    obj = _truong;
                    break;
                case "CHUYENNGANH":
                    obj = _chuyennganh;
                    break;
                case "TUNAM":
                    obj = _tunam;
                    break;
                case "DENNAM":
                    obj = _dennam;
                    break;
                case "ID_HDT":
                    obj = _id_hdt;
                    break;
                case "ID_VB":
                    obj = _id_vb;
                    break;
            }
            return obj;
        }

        /// <summary>
        /// Gán giá trị cho thuộc tính của lớp đối tượng theo string name.
        /// Dùng hàm này để các hàm insert, update có thể dùng câu lệnh for cho các thuộc tính
        /// </summary>
        /// <param name="paramName">The name of the parameter.</param>
        /// <param name="value">The value of the parameter.</param>
        /// <returns>Giá trị của thuộc tính đó.</returns>
        public void SetValueByName(string paramName, object value)
        {
            switch (paramName)
            {
                case "ID_CTQTDT":
                    _id_ctqtdt = (int)value;
                    break;
                case "ID_NHANVIEN":
                    _id_nhanvien = (int)value;
                    break;
                case "TRUONG":
                    _truong = value.ToString();
                    break;
                case "CHUYENNGANH":
                    _chuyennganh = value.ToString();
                    break;
                case "TUNAM":
                    _tunam = value.ToString();
                    break;
                case "DENNAM":
                    _dennam = value.ToString();
                    break;
                case "ID_HDT":
                    _id_hdt = (int)value;
                    break;
                case "ID_VB":
                    _id_vb = (int)value;
                    break;

            }
        }
    }

    class CCTQTDTCollection
    {
        public static string[] _params = { 
                "ID_CTQTDT"
               ,"ID_NHANVIEN"
               ,"TRUONG"
               ,"CHUYENNGANH"
               ,"TUNAM"
               ,"DENNAM"
               ,"ID_HDT"
               ,"ID_VB"
               };


        /// <summary>
        /// Bang luong hien tai cua nhan vien
        /// </summary>
        /// <param name="id_nhanvien"></param>
        /// <returns></returns>
        public static CCTQTDT LayObjectTheoID(int id_ctqtdt)
        {
            CCTQTDT obj;
            string strSQL = "select * from CTQTDT where ";
            strSQL += _params[0] + " = " + id_ctqtdt.ToString();
            obj = GetObjectBySQL(strSQL);
            return obj;
        }

        /// <summary>
        /// Lay qua trinh cua nhan vien
        /// </summary>
        /// <param name="id_nhanvien"></param>
        /// <returns></returns>
        public static DataSet LayQuaTrinhTheoID_NHANVIEN(int id_nhanvien)
        {
            DataSet ds;
            string strSQL = "select * from view_QTDAOTAO where ";
            strSQL += _params[1] + " = " + id_nhanvien.ToString();
            strSQL += " order by " + _params[4] + " asc";
            ds = MITI.BaseDB.Instance.ExecSql_DataSet(strSQL);
            return ds;
        }

        /// <summary>
        /// Tra ve doi tuong CCTQTDT voi cau lenh SQL bat ky dang "Select * from CTQTDT ..."
        /// </summary>
        /// <param name="strSQL"></param>
        /// <returns></returns>
        public static CCTQTDT GetObjectBySQL(string strSQL)
        {
            SqlDataReader objReader = null;
            CCTQTDT obj = null;
            try
            {
                objReader = MITI.BaseDB.Instance.ExecSql_DataReader(strSQL);
                while (objReader.Read())
                {
                    obj = GetObjectByDataReader(objReader);
                }
                if ((objReader != null))
                {
                    objReader.Close();
                }
                MITI.BaseDB.Instance.CloseConnect();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            return obj;
        }

        /// <summary>
        /// Ham nay dung de tao ra doi tuong CCTQTDT voi dau vao la SqlDataReader
        /// </summary>
        /// <param name="objReader"> Dau vao la doi tuong SqlDataReader</param>
        /// <returns></returns>
        public static CCTQTDT GetObjectByDataReader(SqlDataReader objReader)
        {
            CCTQTDT obj = new CCTQTDT();
            for (int i = 0; i < _params.Length; i++)
            {
                obj.SetValueByName(_params[i], objReader.GetValue(i));
            }
            return obj;
        }

        public static long Insert(CCTQTDT objCTQTDT)
        {
            StringBuilder objStringBuilder = new StringBuilder(250);

            objStringBuilder.Append("INSERT INTO CTQTDT VALUES(");
            //Bắt đầu từ i = 1 để không lấy ID
            for (int i = 1; i < _params.Length; i++)
            {
                if (_params[i] == _params[_params.Length - 1])
                    objStringBuilder.Append(MITI.BaseDB.Instance.GetParamPlaceHolder(_params[i], false));
                else
                    objStringBuilder.Append(MITI.BaseDB.Instance.GetParamPlaceHolder(_params[i], true));
            }
            objStringBuilder.Append(")");

            string sInsert = objStringBuilder.ToString();

            SqlCommand cmd = MITI.BaseDB.Instance.CreateCommand(sInsert, CommandType.Text);
            long lRowsAffected = 0;
            try
            {
                {
                    cmd.Transaction = cmd.Connection.BeginTransaction();
                    //Bắt đầu từ i = 1 để không lấy ID
                    for (int i = 1; i < _params.Length; i++)
                    {
                        AddParameter(ref cmd, _params[i], objCTQTDT.GetValueByName(_params[i]));
                    }
                    lRowsAffected = cmd.ExecuteNonQuery();
                }
                cmd.Transaction.Commit();
            }
            catch (System.Exception ex)
            {
                cmd.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                MITI.BaseDB.Instance.CloseConnect();
            }
            return lRowsAffected;
        }

        public static long Update(CCTQTDT objCTQTDT)
        {
            long lRowsAffected = 0;
            string strSQL = "UPDATE CTQTDT SET";
            //Bắt đầu từ i = 1 để không lấy ID
            for (int i = 1; i < _params.Length; i++)
            {
                if (_params[i] == _params[_params.Length - 1])
                    strSQL += " " + _params[i] + " = " + MITI.BaseDB.Instance.GetParamPlaceHolder(_params[i], false);
                else
                    strSQL += " " + _params[i] + " = " + MITI.BaseDB.Instance.GetParamPlaceHolder(_params[i], true);
            }
            //Mệnh đề where lấy ID
            strSQL += " WHERE " + _params[0] + " = " + MITI.BaseDB.Instance.GetParamPlaceHolder(_params[0], false);

            SqlCommand cmd = MITI.BaseDB.Instance.CreateCommand(strSQL, CommandType.Text);
            try
            {
                cmd.Transaction = cmd.Connection.BeginTransaction();
                //Bắt đầu từ i = 1 để không lấy ID
                for (int i = 1; i < _params.Length; i++)
                {
                    AddParameter(ref cmd, _params[i], objCTQTDT.GetValueByName(_params[i]));
                }
                //Mệnh đề where lấy ID
                AddParameter(ref cmd, _params[0], objCTQTDT.GetValueByName(_params[0]));
                lRowsAffected = cmd.ExecuteNonQuery();
                cmd.Transaction.Commit();
            }
            catch (System.Exception ex)
            {
                cmd.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                MITI.BaseDB.Instance.CloseConnect();
            }
            return lRowsAffected;
        }

        public static long Delete(int id)
        {
            long lRowsAffected = 0;
            string strSQL = "DELETE FROM CTQTDT";
            //Mệnh đề where lấy ID
            strSQL += " WHERE " + _params[0] + " = " + id.ToString();

            SqlCommand cmd = MITI.BaseDB.Instance.CreateCommand(strSQL, CommandType.Text);
            try
            {
                cmd.Transaction = cmd.Connection.BeginTransaction();
                lRowsAffected = cmd.ExecuteNonQuery();
                cmd.Transaction.Commit();
            }
            catch (System.Exception ex)
            {
                cmd.Transaction.Rollback();
                throw ex;
            }
            finally
            {
                MITI.BaseDB.Instance.CloseConnect();
            }
            return lRowsAffected;
        }

        protected static SqlParameter AddParameter(ref SqlCommand cmd, string paramName, object value)
        {
            SqlParameter parameter = null;
            switch (paramName)
            {
                //Kieu Int
                case "ID_CTQTDT":
                case "ID_NHANVIEN":
                case "ID_HDT":
                case "ID_VB":
                    parameter = MITI.BaseDB.Instance.CreateParameter(ref cmd, paramName, DbType.Int32, ((int)value == -1 ? DBNull.Value : value));
                    break;
                //Kieu String
                case "TRUONG":
                case "CHUYENNGANH":
                case "TUNAM":
                case "DENNAM":
                    parameter = MITI.BaseDB.Instance.CreateParameter(ref cmd, paramName, DbType.String, (value == null ? DBNull.Value : value));
                    break;
            }
            return parameter;
        }
    }
}


